package com.pet.dao;
/*-----------------------------------
 *   @Author: uni
 *   @Time: 2024/3/18 21:05   
 *   @Description: 
-----------------------------------*/

import com.pet.entity.dos.PictureDO;
import com.pet.entity.dto.PicQueryDTO;
import com.pet.entity.vo.PicTagVO;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PictureDAO {
    @Select("SELECT * FROM pet_picture " +
            "WHERE detail LIKE CONCAT('%', #{detail}, '%') " +
            "AND tag LIKE CONCAT('%', #{tag}, '%') " +
            "ORDER BY ${desc ? 'create_time DESC' : 'create_time ASC'} "+
            "LIMIT #{startIndex}, 10")
    List<PictureDO> selectPictureList(PicQueryDTO pic);

    @Select("SELECT count(*) FROM pet_picture " +
            "WHERE detail LIKE CONCAT('%', #{detail}, '%') " +
            "AND tag LIKE CONCAT('%', #{tag}, '%')")
    Integer countPictureList(PicQueryDTO pic);

    @Select("""
            SELECT p1.detail ,p1.img_url, p2.last_time, p3.tag, p3.total
            FROM pet_picture AS p1
            JOIN (SELECT tag, MAX(create_time) AS last_time FROM pet_picture GROUP BY tag) AS p2
            ON p1.tag = p2.tag AND p1.create_time = p2.last_time
            JOIN (SELECT tag,COUNT(*) AS total FROM pet_picture GROUP BY tag) AS p3
            ON p3.tag = p1.tag;
            """)
    @Results({
            @Result(property = "pic.detail", column = "detail"),
            @Result(property = "pic.imgUrl", column = "img_url"),
            @Result(property = "pic.createTime", column = "last_time"),
            @Result(property = "pic.tag", column = "tag"),
            @Result(property = "total", column = "total")
            // 其他属性映射
    })
    List<PicTagVO> selectTagPicList();

    @Insert("INSERT INTO pet_picture(detail, img_url, create_time, tag) VALUES " +
            "(#{detail}, #{imgUrl}, #{createTime}, #{tag})")
    boolean insert(PictureDO pictureDO);
}
